﻿using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace TestSystem.DAL.News
{
    public class NewsDataAccess : DataAccess
    {
        public static void Update(int newsID, int facultyID, string newsName, string description, string shortDescription, DateTime publishingDate, bool isPublished)
        {
            SqlParameter [] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@NewsID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, newsID),
                    new SqlParameter("@FacultyID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, facultyID),
                    new SqlParameter("@NewsName", SqlDbType.VarChar, 200, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, newsName),
                    new SqlParameter("@Description", SqlDbType.NText, NTEXT, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, description),
                    new SqlParameter("@ShortDescription", SqlDbType.VarChar, 200, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, shortDescription),
                    new SqlParameter("@PublishingDate", SqlDbType.DateTime, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, publishingDate),
                    new SqlParameter("@IsPublished", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, isPublished)
                };
            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "News_Update", paramsArray);
        }

        public static int Insert(int facultyID, string newsName, string description, string shortDescription, DateTime publishingDate, bool isPublished)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@FacultyID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, facultyID),
                    new SqlParameter("@NewsName", SqlDbType.VarChar, 200, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, newsName),
                    new SqlParameter("@Description", SqlDbType.NText, NTEXT, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, description),
                    new SqlParameter("@ShortDescription", SqlDbType.VarChar, 200, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, shortDescription),
                    new SqlParameter("@PublishingDate", SqlDbType.DateTime, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, publishingDate),
                    new SqlParameter("@IsPublished", SqlDbType.Bit, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, isPublished)
                };
            return Convert.ToInt32(SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "News_Insert", paramsArray));
        }

        public static IDataReader GetNewsByFacultyID(int facultyID, int count)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@FacultyID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, facultyID),
                    new SqlParameter("@Count", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, count)
                };
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "News_GetByFacultyID", paramsArray);
        }

        public static IDataReader GetNewsForUniversity(int count)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@Count", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, count)
                };
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "News_GetForUniversity", paramsArray);
        }

        public static IDataReader GetAllNews(int lowerBound, int upperBound)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@LowerBound", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, lowerBound),
                    new SqlParameter("@UpperBound", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, upperBound)
                };
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "News_GetAll", paramsArray);
        }

        public static IDataReader GetNewsByID(int newsID)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@NewsID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, newsID)
                };
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "News_GetByID", paramsArray);
        }
        
        public static void Delete(int newsID)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@NewsID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, newsID)
                };
            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "News_Delete", paramsArray);
        }
        
        public static int GetAllNewsCount()
        {
            return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "News_GetAllCount", null));
        }
    }
}
